package com.beiyou;


import java.io.IOException;
import java.io.InputStream;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.util.*;

/**
 * @author LittleDesire
 * @description 数据库工具类
 * @date 2022-03-04 周五 15:22
 * @since 17
 */
public class DbUtil {
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private final double version = 1.0;
    private String mysqlVersion = "8.0.28";
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=PRC";
    private String username = "root";
    private String password = "123";
    private Connection conn = null;
    // 当前页码
    private long currPage = 1;
    // 每页多少条数据
    private long pageSize = 5;
    // 总页数
    private long pageCount = 0;
    // 总记录条数
    private long recordCount = 0;
    private InputStream is;

    /**
     * 无参构造方法，当该类被new时，进行初始化加载
     */
    public DbUtil() {
        // new 该类对象时，进行读取配置文件。
        is = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
        // 如果 项目目录中没有 jdbc.properties 文件，将进行使用本类中默认的配置。
        if (is == null) {
            try {
                this.conn = DriverManager.getConnection(this.url, this.username, this.password);
                this.mysqlVersion = this.conn.getMetaData().getDatabaseProductVersion();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            init();
        }
    }

    public DbUtil(String driver, String url, String username, String password, Connection conn, int currPage, int pageSize, int pageCount, int recordCount, InputStream is) {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
        this.conn = conn;
        this.currPage = currPage;
        this.pageSize = pageSize;
        this.pageCount = pageCount;
        this.recordCount = recordCount;
        this.is = is;
    }

    /**
     * 分页方法
     *
     * @param sql    查询语句
     * @param params 参数
     * @return map集合
     */
    public Map<String, Object> paging(String sql, Object... params) {
        String csql = sql.replace(sql.substring(6, sql.indexOf("from")), " count(*) ");
        try {
            PreparedStatement ps = this.conn.prepareStatement(csql);
            int index = 1;
            for (Object param : params) {
                ps.setObject(index++, param);
            }
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                // 得到总记录条数 赋值给全局变量 recordCount
                this.recordCount = rs.getLong(1);
            }
            closeConn(null, ps, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        HashMap<String, Object> map = new HashMap<>(5);
        // 放入每页多少条数据, 总记录数，总页数,当前页
        map.put("pageSize", this.pageSize);
        map.put("recordCount", this.recordCount);
        this.pageCount = this.recordCount % this.pageSize == 0 ? this.recordCount / this.pageSize : this.recordCount / this.pageSize + 1;
        map.put("pageCount", this.pageCount);

        if (this.currPage < 1) {
            this.currPage = 1;
        }
        if (this.currPage > this.pageCount) {
            this.currPage = this.pageCount;
        }
        map.put("currPage", this.currPage);


        //保存当前这一页的数据
        List<Map<String, Object>> data = new ArrayList<>();
        String showsql = String.format("%s %s", sql, "limit ?,?");
        try {
            PreparedStatement ps = this.conn.prepareStatement(showsql);
            int index = 1;
            for (Object v : params) {
                ps.setObject(index++, v);
            }
            ps.setLong(index++, this.currPage * this.pageSize - this.pageSize);
            ps.setLong(index++, this.pageSize);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int col = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> mm = new HashMap<>(col);
                for (int i = 1; i <= col; i++) {
                    String lab = rsmd.getColumnLabel(i);
                    mm.put(lab, rs.getObject(lab));
                }
                data.add(mm);
            }
            closeConn(null, ps, rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        map.put("data", data);
        return map;
    }


    /**
     * 生成32位的16进制密文
     *
     * @param string 要加密的字符串
     * @return 密文：32位 的16进制密文
     */
    public String md5(String string) {
        StringBuilder sb = new StringBuilder(32);
        try {
            MessageDigest md5 = MessageDigest.getInstance("md5");
            md5.update(string.getBytes());
            for (byte b : md5.digest()) {
                String format = String.format("%x", b);
                sb.append(format.length() == 2 ? format : "0" + format);
            }
        } catch (NoSuchAlgorithmException e) {
            e.printStackTrace();
        }
        return sb.toString();
    }


    /**
     * 初始化连接
     */
    private void init() {
        Properties prop = new Properties();
        try {
            // 加载配置文件，若文件呢内读取不到连接属性，则进行使用默认值。
            prop.load(this.is);
            this.driver = prop.getProperty("jdbc.driver", this.driver);
            this.url = prop.getProperty("jdbc.url", this.url);
            this.username = prop.getProperty("jdbc.username", this.username);
            this.password = prop.getProperty("jdbc.password", this.password);
            this.pageSize = Integer.parseInt(prop.getProperty("db.pageSize", String.valueOf(this.pageSize)));
            this.conn = DriverManager.getConnection(this.url, this.username, this.password);
            this.mysqlVersion = this.conn.getMetaData().getDatabaseProductVersion();
        } catch (IOException | SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 自定义连接数据库
     *
     * @param host     主机地址
     * @param db       数据库名称
     * @param username 用户名
     * @param password 密码
     * @param port     端口号
     */
    public void getConn(String host, String db, String username, String password, int port) {
        this.url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&serverTimezone=PRC", host, port, db);
        this.username = username;
        this.password = password;
        try {
            conn = DriverManager.getConnection(this.url, this.username, this.password);
            this.mysqlVersion = this.conn.getMetaData().getDatabaseProductVersion();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * CUD操作，增加、删除、修改
     *
     * @param sql    sql语句
     * @param params 传入的值
     * @return 受影响行数
     */
    public int cud(String sql, Object... params) {
        PreparedStatement ps = null;
        int rows = 0;
        try {
            ps = this.conn.prepareStatement(sql);
            int index = 0;
            for (Object param : params) {
                ps.setObject(++index, param);
            }
            rows = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(null, ps, null);
        }
        return rows;
    }

    /**
     * 查询方法，可以给一个完整的sql语句，也可一接收带有 ? 问号的sql语句，
     * 会对第二个参数进行循环遍历放入sql语句中，组合成完整的sql语句
     *
     * @param sql    sql语句
     * @param params 传值sql语句的参数
     * @return 一个Map集合中放入<columName, values>的List集合
     */
    public List<Map<String, Object>> query(String sql, Object... params) {
        ArrayList<Map<String, Object>> list = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = this.conn.prepareStatement(sql);
            int index = 0;
            // 将拿到的参数 params 依次 填入到 sql 语句中的占位符（?）当中
            for (Object param : params) {
                ps.setObject(++index, param);
            }
            rs = ps.executeQuery();
            // 检索ResultSet对象列的数量、类型和属性。 返回: 这个ResultSet对象的列的描述
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                HashMap<String, Object> map = new HashMap<>(rsmd.getColumnCount());
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    // 获取列别名（如果没有别名则是原本列名）
                    String colName = rsmd.getColumnLabel(i);
                    map.put(colName, rs.getObject(colName));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(null, ps, rs);
        }
        return list;
    }

    /**
     * 关闭连接
     *
     * @param conn 数据库连接对象
     * @param ps   预处理操作对象
     * @param rs   结果集对象
     */
    public void closeConn(Connection conn, PreparedStatement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    public void setConn(Connection conn) {
        this.conn = conn;
    }

    @Override
    public String toString() {
        return "DbUtil{" +
                "version=" + version +
                ", mysqlVersion='" + mysqlVersion + '\'' +
                ", driver='" + driver + '\'' +
                ", url='" + url + '\'' +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", conn=" + conn +
                ", currPage=" + currPage +
                ", pageSize=" + pageSize +
                ", pageCount=" + pageCount +
                ", recordCount=" + recordCount +
                ", is=" + is +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        DbUtil dbUtil = (DbUtil) o;
        return currPage == dbUtil.currPage && pageSize == dbUtil.pageSize && pageCount == dbUtil.pageCount && recordCount == dbUtil.recordCount && Objects.equals(mysqlVersion, dbUtil.mysqlVersion) && Objects.equals(driver, dbUtil.driver) && Objects.equals(url, dbUtil.url) && Objects.equals(username, dbUtil.username) && Objects.equals(password, dbUtil.password) && Objects.equals(conn, dbUtil.conn) && Objects.equals(is, dbUtil.is);
    }

    @Override
    public int hashCode() {
        return Objects.hash(version, mysqlVersion, driver, url, username, password, conn, currPage, pageSize, pageCount, recordCount, is);
    }

    public double getVersion() {
        return version;
    }

    public String getMysqlVersion() {
        return mysqlVersion;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public long getCurrPage() {
        return currPage;
    }

    public void setCurrPage(int currPage) {
        this.currPage = currPage;
    }

    public long getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public long getPageCount() {
        return pageCount;
    }

    public void setPageCount(int pageCount) {
        this.pageCount = pageCount;
    }

    public long getRecordCount() {
        return recordCount;
    }

    public void setRecordCount(int recordCount) {
        this.recordCount = recordCount;
    }

    public InputStream getIs() {
        return is;
    }

    public void setIs(InputStream is) {
        this.is = is;
    }
}
